package com.tistory.commons.utils;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tistory.credit.vo.CreditReq;
import com.tistory.login.vo.MemberSearchInfo;


public class XlsReadRealBank {

	private static final Logger log = LoggerFactory.getLogger(XlsReadRealBank.class);
	public static List<CreditReq> parsingExcel(String filepath,String userId,String reqDate,MemberSearchInfo searchInfo,String payProdFee) {
		List<CreditReq>creditList = new ArrayList<CreditReq>();
		try{
			HSSFWorkbook workBook  =  new HSSFWorkbook(new FileInputStream(new File(filepath)));

			int sheetNum	= 0;
			int rowsCnt 	= 0;
			int cellsCnt 	= 0;
			HSSFSheet sheet = null;
			HSSFRow row 	= null;
			HSSFCell cell 	= null;
			String value = "";
			boolean checkListflag = false;
			HashMap<String , Object> params;
			boolean compareProdFree = false;
			sheetNum    = workBook.getNumberOfSheets();

			log.info("\n# sheet num : " + sheetNum);

			for( int loop = 0; loop < sheetNum; loop++){			
				sheet = workBook.getSheetAt(loop);			 
				rowsCnt = sheet.getPhysicalNumberOfRows();

				for( int rownum = rowsCnt; rownum >=1; rownum--){
					CreditReq req = new CreditReq();
					row = sheet.getRow(rownum);

					if(row != null){
						cellsCnt = row.getPhysicalNumberOfCells();

						for(int c =0; c < cellsCnt; c++){
							params = new HashMap<String , Object>();

							value = "";
							cell = row.getCell(c);

							if(cell != null){

								switch (cell.getCellType()) {

								case XSSFCell.CELL_TYPE_FORMULA:				            	
									params.put("CELL_TYPE_FORMULA"+c, cell.getNumericCellValue());				            	
									value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
									break;

								case XSSFCell.CELL_TYPE_STRING:
									params.put("CELL_TYPE_STRING"+c, cell.getStringCellValue());				            	
									value = cell.getStringCellValue();
									break;

								case XSSFCell.CELL_TYPE_BLANK:
									params.put("CELL_TYPE_BLANK"+c, cell.getBooleanCellValue());
									break;

								case XSSFCell.CELL_TYPE_ERROR :
									params.put("CELL_TYPE_ERROR"+c, cell.getErrorCellValue());
									break;

								case XSSFCell.CELL_TYPE_NUMERIC :				            	
									params.put("CELL_TYPE_NUMERIC"+c, cell.getNumericCellValue());				            	
									value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
									break;

								case XSSFCell.CELL_TYPE_BOOLEAN :
									params.put("CELL_TYPE_BOOLEAN"+c, cell.getBooleanCellValue());
									break;

								default:
									break;
								}

								switch (c) {
								case 1:
									req.setUserId(userId);
									req.setTransDate(value);
									req.setFlagCd("20");
									break;
								case 3:
									if(		  searchInfo.getBcNumberFirst().equals(value)
											|| searchInfo.getBcNumberSecond().equals(value)		
											|| searchInfo.getExceptBcNumber().equals(value)
											){
										checkListflag = true;
									}
									break;
								case 4:
									req.setOriProdAmt(value);
									if(checkListflag){	// 자기것만 저장할수 있는 flag
										if(!compareProdFree){
											value = value.replace(",", "");
											payProdFee = payProdFee.replace(",", "");
											if(Long.parseLong(value) >= Long.parseLong(payProdFee)){
												Long minusFee = Long.parseLong(value)-Long.parseLong(payProdFee);
												req.setProdAmt(minusFee.toString());
												compareProdFree = true;
											}else{
												req.setProdAmt(value);	
											}
										}else{
											req.setProdAmt(value);
										}
									}
									
									break;									
								case 7:
									if(value.indexOf("영업부")>-1){
										req.setCardComCd("10");
										req.setCardDetail("비씨카드회수");
										req.setCardCom("비씨카드");
									}else if (value.indexOf("국민")>-1){
										req.setCardComCd("11");
										req.setCardDetail("국민카드회수");
										req.setCardCom("국민카드");
									}else if (value.indexOf("외환카드")>-1){
										req.setCardComCd("12");
										req.setCardCom("외환카드");
									}else if (value.indexOf("SC")>-1){
										req.setCardComCd("13");
										req.setCardDetail("삼성카드회수");
										req.setCardCom("삼성카드");
									}else if (value.indexOf("여의도")>-1){
										req.setCardComCd("15");
										req.setCardDetail("현대카드회수");
										req.setCardCom("현대카드");
									}else if (value.indexOf("강남")>-1){
										req.setCardComCd("16");
										req.setCardDetail("롯데카드회수");
										req.setCardCom("롯데카드");
									}else if (value.indexOf("신한")>-1){
										req.setCardComCd("17");
										req.setCardDetail("신한카드회수");
										req.setCardCom("신한카드");
									}else if (value.indexOf("전북")>-1){
										req.setCardComCd("18");
									}else if (value.indexOf("외환")>-1){
										req.setCardComCd("19");
										req.setCardDetail("하나카드회수");
										req.setCardCom("하나카드");
									}else if (value.indexOf("농협")>-1){
										req.setCardComCd("20");
										req.setCardDetail("농협카드회수");
										req.setCardCom("농협카드");
									}else{
										req.setCardComCd("21");
									}									

									break;

								default:
									break;
								}								
							} 

						} // for(c) 문
					}
					if(checkListflag){	// 자기것만 저장할수 있는 flag
						creditList.add(req);
						checkListflag = false;
					}
				} // for(r) 문
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return creditList;
	}

}